<?php

declare(strict_types=1);
/**
 * This file is part of habo_new_media.
 *
 * @link     https://www.berlen.cn/
 * @contact  tech@berlen.cn
 * @license  http://gitlab.rehou.cn/habo/habo_new_media/-/blob/master/README.md
 */
namespace app\command;

use app\enum\CommonEnum;
use app\model\ClientAccountModel;
use app\model\PortModel;
use app\model\ProviderAdvSummaryModel;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
use think\Exception;
use think\facade\Db;

class ProviderAdvSummaryWork extends BaseWork
{
    protected function configure()
    {
        // 指令配置
        $this->setName('provider_adv_summary_work');
        $this->addArgument('media_id', Argument::REQUIRED, '媒体id');
        $this->setDescription('供应商与账户汇总');
    }

    protected function execute(Input $input, Output $output)
    {
        set_time_limit(0);
        ini_set('memory_limit', '-1');
        $output->writeln('=======供应商与账户汇总开始' . date('Y-m-d H:i:s', time()) . '=======');

        $mediaId = intval($input->getArgument('media_id'));
        $startDate = date('Y-m-01', strtotime('-1 month'));
        $endDate = date('Y-m-t', strtotime('-1 month'));
        $startDateInt = intval(date('Ym01', strtotime('-1 month')));
        $endDateInt = intval(date('Ymt', strtotime('-1 month')));

        try {
            /*$sql = "select b.advertiser_id,b.pid,b.`level`,start_date,end_date from (select advertiser_id,now_port_id as pid,0 as level,start_date,end_date from habo_account_transfer where start_date<='{$endDate}' and end_date>='{$startDate}'
                    union all
                    select advertiser_id,port_id as pid,1 as level,0 as start_date,0 as end_date from habo_account_port where id in (select a.mid from (select max(id) as mid,advertiser_id from habo_account_port group by advertiser_id) as a) ) b  group by b.advertiser_id";*/

            $sql = "select b.advertiser_id,b.pid,b.`level`,start_date,end_date,media_id from (select advertiser_id,now_port_id as pid,0 as level,start_date,end_date,media_id from habo_account_transfer where start_date<='{$endDate}' and end_date>='{$startDate}' and media_id = {$mediaId}
                    union all
                    select advertiser_id,port_id as pid,1 as level,0 as start_date,0 as end_date,media_id from habo_account_port where id in (select a.mid from (select max(id) as mid,advertiser_id from habo_account_port where media_id = {$mediaId} group by advertiser_id) as a) ) b  group by b.advertiser_id";

            $list = Db::query($sql);
            if (! $list) {
                throw new Exception('=======供应商与账户汇总失败没有数据' . date('Y-m-d H:i:s', time()) . '=======');
            }

            $advList = $this->_getAccountList(array_column($list, 'advertiser_id'));
            if (! $advList) {
                throw new Exception('=======供应商与账户汇总失败账户列表没有数据' . date('Y-m-d H:i:s', time()) . '=======');
            }

            $portList = $this->_getAllPortList();

            if ($mediaId == CommonEnum::OE_MEDIA) {
                $costList = $this->_getOeCostListByDate($startDateInt, $endDateInt);
            } elseif ($mediaId == CommonEnum::KS_MEDIA) {
                $costList = $this->_getKsCostListByDate($startDateInt, $endDateInt);
            }

            $allData = [];
            foreach ($list as $v) {
                $info = $advList[$v['advertiser_id']] ?? '';
                $portInfo = $portList[$v['pid']] ?? '';

                if (! $info || ! $portInfo) {
                    continue;
                }

                $cost = $costList[$v['advertiser_id']]['total_cost'] ?? 0;
                $cashCost = $costList[$v['advertiser_id']]['total_cash_cost'] ?? 0;

                if ($cost <= 0 && $cashCost <= 0) {
                    continue;
                }

                if ($v['start_date'] > $v['end_date']) {
                    $startDateInt = intval(date('Ymd', strtotime($v['start_date'])));
                }

                $allData[] = [
                    'client_id' => $info['client_id'],
                    'account_id' => $info['id'],
                    'adv_id' => $v['advertiser_id'],
                    'media_id' => $info['media_id'],
                    'port_id' => $v['pid'],
                    'provider_id' => $portInfo['provider_id'],
                    'start_date' => $startDateInt,
                    'end_date' => $endDateInt,
                    'cost' => $cost,
                    'cash_cost' => $cashCost,
                    'create_time' => time(),
                ];
            }

            if ($allData) {
                (new ProviderAdvSummaryModel())->insertAll($allData, 10000);
            }
        } catch (\Exception $e) {
            $output->writeln('=======供应商与账户汇总失败' . $e->getMessage() . date('Y-m-d H:i:s', time()) . '=======');
        }

        $output->writeln('=======供应商与账户汇总结束' . date('Y-m-d H:i:s', time()) . '=======');
    }

    private function _getOeCostListByDate(int $startDate = 0, int $endDate = 0): array
    {
        $mediaId = CommonEnum::OE_MEDIA;
        $sql = "SELECT
                    advertiser_id,
                    sum( cost ) AS total_cost,
                    sum( cash_cost ) AS total_cash_cost,
                    sum( reward_cost ) AS total_reward_cost 
                FROM
                    habo_account_daily_stat 
                WHERE
                    stat_date BETWEEN {$startDate}  
                    AND {$endDate}  
                    AND media_id = {$mediaId} 
                GROUP BY
                    advertiser_id 
                HAVING
                    total_cost > 0 
                    OR total_cash_cost > 0 
                    OR total_reward_cost > 0";
        $res = Db::query($sql);
        return array_column($res, null, 'advertiser_id');
    }

    private function _getKsCostListByDate(int $startDate = 0, int $endDate = 0): array
    {
        $sql = "SELECT
                    advertiser_id,
                    sum( daily_charge + order_total_charged) AS total_cost,
                    sum( real_charged + order_real_charged) AS total_cash_cost 
                FROM
                    habo_ks_advertiser_daily_stat 
                WHERE
                    stat_date BETWEEN {$startDate} 
                    AND {$endDate} 
                GROUP BY
                    advertiser_id 
                HAVING
                    total_cost > 0 
                    OR total_cash_cost > 0";
        $res = Db::query($sql);
        return array_column($res, null, 'advertiser_id');
    }

    private function _getAllPortList(): array
    {
        return (new PortModel())->column('*', 'id');
    }

    private function _getAccountList(array $advIds = []): array
    {
        return (new ClientAccountModel())->whereIn('advertiser_id', $advIds)->column('*', 'advertiser_id');
    }
}
